package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO;
import com.ph.irp.model.PBaseAssistantmaterialCulture;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialCultureDAOImpl implements
		PBaseAssistantmaterialCultureDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = 0L;
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * @author yan.jian@phinfoservices.com
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#addAssistantmaterialCulture(com.ph.irp.model.PBaseAssistantmaterialCulture)
	 * @param com.ph.irp.model.PBaseAssistantmaterialCulture
	 * @return void
	 * @throws Throwable
	 */
	@Override
	public void addAssistantmaterialCulture(
			PBaseAssistantmaterialCulture culture) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("insert into p_base_assistantmaterial_culture(cultureCode, cultureName) values(?, ?)");
			pStmt.setString(1, culture.getCultureCode());
			pStmt.setString(2, culture.getCultureName());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/**
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#deleteAssistantmaterialCulture(int[])
	 * @author yan.jian@infoservices.com
	 * @param int Array cultureIds
	 * @return void
	 * @throws Throwable
	 */
	@Override
	public void deleteAssistantmaterialCulture(String cultureId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("delete from p_base_assistantmaterial_culture where cultureID = ?");
			pStmt.setString(1, cultureId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/**
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#getAssistantmaterialCultures(java.lang.String,
	 *      java.lang.String)
	 * @author yan.jian@phinfoservices.com
	 * @param cultureCode
	 * @return List<PBaseAssistantmaterialCulture>
	 * @throws Throwable
	 */
	@Override
	public List<PBaseAssistantmaterialCulture> getAssistantmaterialCulturesByCode(
			String cultureCode) throws Throwable {
		List<PBaseAssistantmaterialCulture> list = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_culture where CultureCode like ?");
			pStmt.setString(1, "%" + cultureCode + "%");
			rs = pStmt.executeQuery();
			list = turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return list;
	}

	/**
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#getAssistantmaterialCulturesByName(java.lang.String)
	 * @author yan.jian@phinfoservices.com
	 * @param cultureName
	 * @return List<PBaseAssistantmaterialCulture>
	 * @throws Throwable
	 */
	@Override
	public List<PBaseAssistantmaterialCulture> getAssistantmaterialCulturesByName(
			String cultureName) throws Throwable {
		List<PBaseAssistantmaterialCulture> list = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_culture where CultureName like ?");
			pStmt.setString(1, "%" + cultureName + "%");
			rs = pStmt.executeQuery();
			list = turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return list;
	}

	/**
	 * 根据Id查询特定的culture
	 * 
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#getAssistantmaterialCultureById(int)
	 * @author yan.jian@phinfoservices.com
	 * @param cultureId
	 * @return PBaseAssistantmaterialCulture
	 * @throws Throwable
	 */
	@Override
	public PBaseAssistantmaterialCulture getAssistantmaterialCultureById(
			int cultureId) throws Throwable {
		PBaseAssistantmaterialCulture culture = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_culture where CultureID = ?");
			pStmt.setInt(1, cultureId);
			rs = pStmt.executeQuery();
			List<PBaseAssistantmaterialCulture> cultures = this
					.turnResultSetToList(rs);
			if(cultures.size() > 0)
				culture = cultures.get(0);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return culture;
	}

	/**
	 * 查处所有的cultur
	 * 
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#getAllAssistantmaterialCulture()
	 * @author yan.jian@phinfoservices.com
	 * @return List<PBaseAssistantmaterialCulture>
	 * @throws Throwable
	 */
	@Override
	public List<PBaseAssistantmaterialCulture> getAllAssistantmaterialCultures()
			throws Throwable {
		List<PBaseAssistantmaterialCulture> list = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_culture");
			rs = pStmt.executeQuery();
			list = turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return list;
	}

	/**
	 * @author yan.jian@phinfoservices.com
	 * @param cultureCode
	 * @param cultureName
	 * @return List<PBaseAssistantmaterialCulture>
	 * @throws Throwable
	 */
	@Override
	public List<PBaseAssistantmaterialCulture> getAssistantmaterialCulturesByCodeAndName(
			String cultureCode, String cultureName) throws Throwable {
		List<PBaseAssistantmaterialCulture> list = null;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_culture where cultureCode like ? and cultureName like ?");
			pStmt.setString(1, "%" + cultureCode + "%");
			pStmt.setString(2, "%" + cultureName + "%");
			rs = pStmt.executeQuery();
			list = turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	/**
	 * @see com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCultureDAO#updateAssistantmaterialCulture(com.ph.irp.model.PBaseAssistantmaterialCulture)
	 * @author yan.jian@phinfoservices.com
	 * @param com.ph.irp.model.PBaseAssistantmaterialCulture
	 * @return void
	 * @throws Throwable
	 */
	@Override
	public void updateAssistantmaterialCulture(
			PBaseAssistantmaterialCulture culture) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("update p_base_assistantmaterial_culture "
							+ "set cultureCode = ?, cultureName = ? where cultureID = ?");
			pStmt.setString(1, culture.getCultureCode());
			pStmt.setString(2, culture.getCultureName());
			pStmt.setInt(3, culture.getCultureId());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/**
	 * 将查询出的ResultSet的数据提取到List中
	 * 
	 * @param rs
	 * @return void
	 * @throws Throwable
	 * @author yan.jian@phinfoservices.com
	 */
	public List<PBaseAssistantmaterialCulture> turnResultSetToList(ResultSet rs)
			throws Throwable {
		List<PBaseAssistantmaterialCulture> list = new ArrayList<PBaseAssistantmaterialCulture>();
		while (rs.next()) {
			PBaseAssistantmaterialCulture pBaseAssistantmaterialCulture = new PBaseAssistantmaterialCulture();
			pBaseAssistantmaterialCulture.setCultureId(rs.getInt("CultureID"));
			pBaseAssistantmaterialCulture.setCultureCode(rs
					.getString("CultureCode"));
			pBaseAssistantmaterialCulture.setCultureName(rs
					.getString("CultureName"));
			list.add(pBaseAssistantmaterialCulture);
		}
		return list;
	}

}
